"""
数据库结构验证脚本
检查数据库表结构与模型定义是否一致
"""
import os
import sys

# 添加项目根目录到Python路径
current_dir = os.path.dirname(os.path.abspath(__file__))
parent_dir = os.path.dirname(current_dir)
sys.path.insert(0, parent_dir)

from app import create_app, db
from app.models import user, project, customer
from sqlalchemy import inspect, text

def check_table_exists(table_name):
    """检查表是否存在"""
    inspector = inspect(db.engine)
    return table_name in inspector.get_table_names()

def check_column_exists(table_name, column_name):
    """检查列是否存在"""
    inspector = inspect(db.engine)
    columns = [col['name'] for col in inspector.get_columns(table_name)]
    return column_name in columns

def validate_database():
    """验证数据库结构"""
    app = create_app()
    with app.app_context():
        issues = []
        
        # 检查User模型相关表
        if not check_table_exists('users'):
            issues.append("缺少 'users' 表")
        else:
            if not check_column_exists('users', 'updated_at'):
                issues.append("'users' 表缺少 'updated_at' 列")
            if not check_column_exists('users', 'created_at'):
                issues.append("'users' 表缺少 'created_at' 列")
        
        # 检查Notification表
        if not check_table_exists('notification'):
            issues.append("缺少 'notification' 表")
        else:
            if not check_column_exists('notification', 'updated_at'):
                issues.append("'notification' 表缺少 'updated_at' 列")
            if not check_column_exists('notification', 'created_at'):
                issues.append("'notification' 表缺少 'created_at' 列")
        
        # 检查UserActivity表
        if not check_table_exists('user_activity'):
            issues.append("缺少 'user_activity' 表")
        else:
            if not check_column_exists('user_activity', 'updated_at'):
                issues.append("'user_activity' 表缺少 'updated_at' 列")
            if not check_column_exists('user_activity', 'created_at'):
                issues.append("'user_activity' 表缺少 'created_at' 列")
        
        # 检查Project表
        if not check_table_exists('project'):
            issues.append("缺少 'project' 表")
        else:
            if not check_column_exists('project', 'updated_at'):
                issues.append("'project' 表缺少 'updated_at' 列")
            if not check_column_exists('project', 'created_at'):
                issues.append("'project' 表缺少 'created_at' 列")
        
        # 检查ProjectTask表
        if not check_table_exists('project_task'):
            issues.append("缺少 'project_task' 表")
        else:
            if not check_column_exists('project_task', 'updated_at'):
                issues.append("'project_task' 表缺少 'updated_at' 列")
            if not check_column_exists('project_task', 'created_at'):
                issues.append("'project_task' 表缺少 'created_at' 列")
        
        # 检查Customer表
        if not check_table_exists('customer'):
            issues.append("缺少 'customer' 表")
        else:
            if not check_column_exists('customer', 'updated_at'):
                issues.append("'customer' 表缺少 'updated_at' 列")
            if not check_column_exists('customer', 'created_at'):
                issues.append("'customer' 表缺少 'created_at' 列")
        
        # 检查FollowUp表
        if not check_table_exists('followups'):
            issues.append("缺少 'followups' 表")
        else:
            if not check_column_exists('followups', 'updated_at'):
                issues.append("'followups' 表缺少 'updated_at' 列")
            if not check_column_exists('followups', 'created_at'):
                issues.append("'followups' 表缺少 'created_at' 列")
        
        # 检查ProjectMilestone表
        if not check_table_exists('project_milestone'):
            issues.append("缺少 'project_milestone' 表")
        else:
            if not check_column_exists('project_milestone', 'updated_at'):
                issues.append("'project_milestone' 表缺少 'updated_at' 列")
            if not check_column_exists('project_milestone', 'created_at'):
                issues.append("'project_milestone' 表缺少 'created_at' 列")
        
        return issues

def fix_database():
    """尝试修复数据库结构问题"""
    app = create_app()
    with app.app_context():
        # 这里可以添加自动修复逻辑
        # 例如：添加缺失的列
        pass

if __name__ == '__main__':
    print("正在验证数据库结构...")
    issues = validate_database()
    
    if issues:
        print(f"\n发现 {len(issues)} 个问题:")
        for i, issue in enumerate(issues, 1):
            print(f"{i}. {issue}")
        
        print("\n建议:")
        print("1. 运行数据库迁移脚本修复这些问题")
        print("2. 或者使用Flask-Migrate管理数据库结构变化")
        sys.exit(1)
    else:
        print("\n数据库结构验证通过！")
        sys.exit(0)